package org.hibernate.dialect;

import java.sql.Types;

import org.hibernate.dialect.SQLServerDialect;

/**
 * Author: Wesley Wu
 * wesley@buysou.com
 * Date: 2006-4-29
 * 
 * Andrei.Chirila Comment [2009-07-08] :
 * 
 * This dialect is required due to bug in the SQLServerDialect for
 * SQLServer 2005 concerning pagination and the use of the row_number() function. 
 * Currently, the SQLServerDialect (common for versions 2000 and 2005) is using the TOP
 * which loads all data until the current page. This bug is still unsolved in 
 * Hibernate 3.4.
 */
public class SQLServer2005Dialect extends SQLServerDialect {
  public SQLServer2005Dialect() {
    super();

    registerColumnType(Types.VARCHAR, 1073741823, "NVARCHAR(MAX)");
    registerColumnType(Types.VARCHAR, 2147483647, "VARCHAR(MAX)");
    registerColumnType(Types.VARBINARY, 2147483647, "VARBINARY(MAX)");
  }

  /**
   * Add a LIMIT clause to the given SQL SELECT
   *
   * The LIMIT SQL will look like:
   *
   * WITH query AS
   * (SELECT TOP last ROW_NUMBER() OVER (ORDER BY orderby) as __hibernate_row_nr__, ... original_query)
   * SELECT *
   * FROM query
   * WHERE __hibernate_row_nr__ > offset
   * ORDER BY __hibernate_row_nr__
   *
   * @param querySqlString The SQL statement to base the limit query off of.
   * @param offset         Offset of the first row to be returned by the query (zero-based)
   * @param last           Maximum number of rows to be returned by the query
   * @return A new SQL statement with the LIMIT clause applied.
   */
  @Override
  public String getLimitString(String querySqlString, int offset, int last) {
    /*
         * WITH query AS
         *     (SELECT TOP last ROW_NUMBER() OVER (ORDER BY orderby) as __hibernate_row_nr__, ... original_query)
         * SELECT *
         * FROM query
         * WHERE __hibernate_row_nr__ > offset
         * ORDER BY __hibernate_row_nr__
         */
    StringBuffer pagingBuilder = new StringBuffer();
    String orderby = getOrderByPart(querySqlString);
    String distinctStr = "";

    String loweredString = querySqlString.toLowerCase();
    String sqlPartString = querySqlString;
    if (loweredString.trim().startsWith("select")) {
      int index = 6;
      if (loweredString.startsWith("select distinct")) {
        distinctStr = "DISTINCT ";
        index = 15;
      }
      sqlPartString = sqlPartString.substring(index);
    }
    pagingBuilder.append(sqlPartString);

    // if no ORDER BY is specified use fake ORDER BY field to avoid errors
    if (orderby == null || orderby.length() == 0) {
      orderby = "ORDER BY CURRENT_TIMESTAMP";
    }

    StringBuffer result = new StringBuffer();
    result.append("WITH query AS (SELECT ")
        .append(distinctStr)
        .append("TOP ")
        .append(offset + last)
        .append(" ROW_NUMBER() OVER (")
        .append(orderby)
        .append(") as __hibernate_row_nr__, ")
        .append(pagingBuilder)
        .append(") SELECT * FROM query WHERE __hibernate_row_nr__ > ")
        .append(offset)
        .append(" ORDER BY __hibernate_row_nr__");

    return result.toString();
  }

  @Override
  public boolean supportsLimit() {
    return true;
  }

  @Override
  public boolean supportsLimitOffset() {
    return true;
  }

  @Override
  public boolean useMaxForLimit() {
    return false;
  }

  static String getOrderByPart(String sql) {
    String loweredString = sql.toLowerCase();
    int orderByIndex = loweredString.indexOf("order by");
    if (orderByIndex != -1) {
      // if we find a new "order by" then we need to ignore
      // the previous one since it was probably used for a subquery
      return sql.substring(orderByIndex);
    } else {
      return "";
    }
  }
}
